package require sqlite3 3.6.19
package require tdbc::sqlite3

tdbc::sqlite3::connection create db docs/wubchain.sqlite
catch {
    db allrows {DROP TABLE users}
    db allrows {DROP TABLE user_subjects}
}
db allrows {
    CREATE TABLE users (
			username TEXT NOT NULL,
			password TEXT NOT NULL,
			email TEXT NOT NULL DEFAULT "",
			sid TEXT NOT NULL DEFAULT "",
			last_login INT NOT NULL DEFAULT 0,
			emoticons INT NOT NULL DEFAULT 1,
			nick_colors INT NOT NULL DEFAULT 1,
			lines INT NOT NULL DEFAULT 20,
			auto_update INT NOT NULL DEFAULT 1,
			user_list INT NOT NULL DEFAULT 1,
			mobile INT NOT NULL DEFAULT 0,
			from_language TEXT NOT NULL DEFAULT "", 
			to_language  TEXT NOT NULL DEFAULT "",
			time_stamps INT NOT NULL DEFAULT 1,
			time_zone TEXT NOT NULL DEFAULT ":GMT",
			hide_logon_logoff INT NOT NULL DEFAULT 1,
			admin INT NOT NULL DEFAULT 0,
			state TEXT NOT NULL DEFAULT "",
			ipaddr TEXT NOT NULL DEFAULT "",
			port INT NOT NULL DEFAULT 0,
			user_agent TEXT NOT NULL DEFAULT "",
			PRIMARY KEY (username))
}
db allrows {
    CREATE TABLE user_subjects (
				username TEXT NOT NULL,
				id INT NOT NULL,
				subject TEXT,
				PRIMARY KEY (username, id),
				FOREIGN KEY (username) REFERENCES users(username))
}

foreach {username password admin} {test test 1 test2 test2 1 test3 test3 0} {
    db allrows {INSERT INTO users (username, password, admin) values (:username, :password, :admin)}
}

db close
